Detailed explanation of python mysql

您所在的位置：网站首页 › mysql 知乎 › Detailed explanation of python mysql

Detailed explanation of python mysql

#Detailed explanation of python mysql| 来源: 网络整理| 查看: 265

The python module mysql-connector manages the database

Article Directory The python module mysql-connector manages the database1 Introduction2. Install3. Usage3.1 Create a database connection3.2 Create a database3.3 Check if the database exists3.4 Create a data table3.5 Check if the data table already exists3.6 Primary key setting3.7 Insert data3.8 Batch Insert3.9 Query data3.9.1 Get all records3.9.2 If you only want to read one piece of data, you can use the fetchone() method3.9.3 Sorting3.9.4 To prevent **SQL injection attacks** from occurring in database queries, we can use the %s placeholder to escape the query conditions. 3.10 Deleting records3.11 Update table data3.12 Delete table

1 Introduction

MySQL is the most popular relational database management system, mysql-connector is used to connect to MySQL, and mysql-connector is the official driver provided by MySQL.

2. Install $ pip install mysql-connector

Note: If your MySQL is version 8.0, the authentication method of the password plug-in has changed. The earlier version is mysql_native_password, and the version 8.0 is caching_sha2_password, so some changes need to be made:

First modify the my.ini configuration:

[mysqld] default_authentication_plugin=mysql_native_password

Then execute the following command under mysql to change the password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password'; 3. Usage 3.1 Create a database connection import mysql.connector mydb = mysql.connector.connect( host="192.168.1.190", user="root", passwd="Password01!" ) print(mydb)

output:

python my2.py 3.2 Create a database import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456" ) mycursor = mydb. cursor() mycursor.execute("CREATE DATABASE runoob_db;") 3.3 Check if the database exists import mysql.connector mydb = mysql.connector.connect( host="192.168.1.190", user="root", passwd="Password01!" ) mycursor = mydb. cursor() mycursor. execute("SHOW DATABASES;") for x in mycursor: print(x)

output:

$ python my1.py (u'information_schema',) (u'api_server',) (u'api_server_test',) (u'horus',) (u'mgm',) (u'mysql',) (u'mysqlmanager',) (u'performance_schema',) (u'redismanager',) (u'sys',) 3.4 Create a data table import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))") 3.5 Check if the data table already exists import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() mycursor. execute("SHOW TABLES") for x in mycursor: print(x) 3.6 Primary key setting

Add a primary key to the sites table

Create a primary key for the table

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))") 3.7 Insert data import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = ("RUNOOB", "https://www.runoob.com") mycursor. execute(sql, val) mydb.commit() # The content of the data table has been updated, this statement must be used print(mycursor.rowcount, "The record was inserted successfully.") 3.8 Batch Insert

Batch insert using executemany() method

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = [ ('Google', 'https://www.google.com'), ('Github', 'https://www.github.com'), ('Taobao', 'https://www.taobao.com'), ('stackoverflow', 'https://www.stackoverflow.com/') ] mycursor.executemany(sql, val) mydb.commit() # The content of the data table has been updated, this statement must be used print(mycursor.rowcount, "The record was inserted successfully.") print("1 record has been inserted, ID:", mycursor.lastrowid) 3.9 Query data 3.9.1 Get all records import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() mycursor. execute("SELECT * FROM sites") myresult = mycursor.fetchall() # fetchall() fetches all records for x in myresult: print(x)

output:

(1, 'RUNOOB', 'https://www.runoob.com') (2, 'Google', 'https://www.google.com') (3, 'Github', 'https://www.github.com') (4, 'Taobao', 'https://www.taobao.com') (5, 'stackoverflow', 'https://www.stackoverflow.com/') (6, 'Zhihu', 'https://www.zhihu.com') 3.9.2 If you only want to read one piece of data, you can use the fetchone() method import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() mycursor. execute("SELECT * FROM sites") myresult = mycursor. fetchone() print(myresult)

The output is:

(1, 'RUNOOB', 'https://www.runoob.com') 3.9.3 Sorting

You can use the ORDER BY statement to sort query results. The default sorting method is ascending, and the keyword is ASC . If you want to set descending sorting, you can set the keyword DESC .

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "SELECT * FROM sites ORDER BY name" #sql = "SELECT * FROM sites ORDER BY name DESC" #Descending order #mycursor.execute("SELECT * FROM sites LIMIT 3") #Set the query data volume through the "LIMIT" statement #mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1") The keyword used is OFFSET to specify the starting position mycursor. execute(sql) myresult = mycursor. fetchall() for x in myresult: print(x) 3.9.4 To prevent SQL injection attacks in database queries , we can use the %s placeholder to escape query conditions.

ascending order

The output is:

(3, 'Github', 'https://www.github.com') (2, 'Google', 'https://www.google.com') (1, 'RUNOOB', 'https://www.runoob.com') (5, 'stackoverflow', 'https://www.stackoverflow.com/') (4, 'Taobao', 'https://www.taobao.com') (6, 'Zhihu', 'https://www.zhihu.com') 3.10 Deleting records import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "DELETE FROM sites WHERE name = 'stackoverflow'" mycursor. execute(sql) mydb. commit() print(mycursor.rowcount, "records deleted")

In order to prevent SQL injection attacks in database queries, we can use the %s placeholder to escape the condition of the delete statement

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "DELETE FROM sites WHERE name = %s" na = ("stackoverflow", ) mycursor. execute(sql, na) mydb. commit() print(mycursor.rowcount, "records deleted") 3.11 Update table data import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'" mycursor. execute(sql) mydb. commit() print(mycursor.rowcount, "A record has been modified")

The output is:

1 record was modified

In order to prevent SQL injection attacks in database queries, we can use the %s placeholder to escape the condition of the update statement

import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "UPDATE sites SET name = %s WHERE name = %s" val = ("Zhihu", "ZH") mycursor. execute(sql, val) mydb. commit() print(mycursor.rowcount, "A record has been modified")

The output is:

1 record was modified 3.12 Delete table import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456", database="runoob_db" ) mycursor = mydb. cursor() sql = "DROP TABLE IF EXISTS sites" # delete data table sites mycursor. execute(sql)

refer to:

Python MySQL - mysql-connector driverMySQL Connector/Python Developer Guide

【本文地址】

Detailed explanation of python mysql

Detailed explanation of python mysql

今日新闻

推荐新闻